import pymysql
import openpyxl

db = pymysql.connect(host = "192.168.13.92", port = 3306, user = "test",
                     password = "123456", database = "school_db", charset = "utf8")
cursor = db.cursor()

# sql = "create table user_info(id varchar(20), name varchar(20), gender varchar(20), grade int)"
# cursor.execute(sql)

#wb = openpyxl.Workbook()

wb = openpyxl.load_workbook('user.xlsx')
# sheet1 = wb.create_sheet(title = "user_info")
sheet1 = wb['user_info']
# sheet1.sheet_properties.index = 0

# sheet_to_delete = wb['Sheet']
# wb.remove(sheet_to_delete)

# data = [
#     ["001", "李四", "男", 90],
#     ["002", "王五", "女", 85],
#     ["003", "赵六", "男", 92],
#     ["004", "徐六", "男", 92],
#     ["005", "张六", "男", 92]
# ]
# for row in data:
#     sheet1.append(row)
"""
将Excel表中数据按行读取到列表，再将列表中数据通过sql指令传入数据库
"""
row_data = []
max_col = sheet1.max_column
max_row = sheet1.max_row
for i in range(1, max_row + 1):
    for col in range(1, max_col + 1):
        value = sheet1.cell(row=i, column=col).value
        row_data.append(value)
    sql = f"insert into user_info values('{row_data[0]}','{row_data[1]}','{row_data[2]}',{row_data[3]})"
    #print(row_data)
    cursor.execute(sql)
    db.commit()
    row_data = []
wb.save("user.xlsx")

cursor.close()
db.close()